package jt.classic.system.workflow;

import jt.classic.app.util.ListRange;

import jt.classic.system.ISystem;
import jt.classic.system.SimpleAction;
import jt.classic.system.database.SystemDataSource;
import jt.classic.system.group.GroupManager;
import jt.classic.system.group.IGroup;
import jt.classic.system.role.IRole;
import jt.classic.system.user.IUser;
import jt.classic.system.user.UserManager;

import net.sf.json.JSONObject;

import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import org.limp.basework.SimpleBean;
import org.limp.basework.impl.CommonBean4HSQ;

import org.limp.mine.Controller;
import org.limp.mine.DataTrimmer;
import org.limp.mine.DateTrimmer;
import org.limp.mine.HTMLTool;
import org.limp.mine.Label;
import org.limp.mine.QueryJack;
import org.limp.mine.QueryJackFactory;
import org.limp.mine.StringTool;

import java.io.IOException;
import java.io.PrintWriter;

import java.sql.Connection;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


/**
 * 工作流程办理列表控制器
 *
 * @author mido
 *
 */
public class WorkflowListAction extends SimpleAction {
    /**
    * 我的Index页面
    *
    * @param mapping
    * @param form
    * @param request
    * @param response
    * @return
    * @throws Exception
    */
    public ActionForward myindex(ActionMapping mapping, ActionForm form,
        HttpServletRequest request, HttpServletResponse response)
        throws Exception {
        ActionForward forward = null;
        Connection conn = null;

        String key = StringTool.checkString(request.getParameter("key"));

        IUser user = ISystem.getSessionUser(request);

        try {
            SystemDataSource ds = SystemDataSource.getInstance();
            conn = ds.getConnection();

            WObjectRegister register = WObjectRegister.getInstance();

            WObject object = register.findWObject(key);

            WorkflowManager manager = new WorkflowManager(conn);

            request.setAttribute("object", object);

            forward = new ActionForward();
            forward.setPath(object.iview4index());
        } catch (Exception ex) {
            forward = mapping.findForward("error");
            ISystem.catchException(request, this, ex);
        } finally {
            SystemDataSource.closeConnection(conn);
        }

        return forward;
    }

    /**
     * 我的传阅列表
     *
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward myencycliclist(ActionMapping mapping, ActionForm form,
        HttpServletRequest request, HttpServletResponse response)
        throws Exception {
        ActionForward forward = null;
        Connection conn = null;

        IUser user = ISystem.getSessionUser(request);

        String key = StringTool.checkString(request.getParameter("key"));

        request.setAttribute("key", key);

        SimpleBean parm = new CommonBean4HSQ(request);

        String qname = StringTool.checkString(parm.getResource().get("qname"));

        String qdate = StringTool.checkString(request.getParameter("qdate"));

        qdate = DateTrimmer.formatDateForVarchar(qdate);

        String flumeid = user.iallgroupflumeid(",");

        String groupIds = UserManager.iallgroupflumeid(flumeid);

        String sqlwhere = " ( ((WF_CC.OBJ_ID = '" + user.id() +
            "' AND OBJ_TYPE = 'user') OR (WF_CC.OBJ_ID IN (" + groupIds +
            ") AND OBJ_TYPE = 'group')) ";

        if (!qname.equals("")) {
            sqlwhere += (" AND WF_ACTIVITY.A_TITLE LIKE '%" + qname + "%' ");
        }

        if (!qdate.equals("")) {
            sqlwhere += (" AND WF_CC.CC_DATE LIKE '%" + qdate + "%' ");
        }

        if (!key.equals("")) {
            sqlwhere += (" AND WF_CC.WOBJECT = '" + key + "' ");
        }

        sqlwhere += ")";

        String sql1 =
            "SELECT COUNT(DISTINCT(WF_ACTIVITY.A_ID)) FROM WF_ACTIVITY,WF_CC WHERE WF_ACTIVITY.A_ID = " +
            "WF_CC.A_ID AND " + sqlwhere;
        String sql2 =
            "SELECT DISTINCT(WF_ACTIVITY.A_ID) AS A_ID FROM WF_ACTIVITY,WF_CC WHERE WF_ACTIVITY.A_ID = " +
            "WF_CC.A_ID AND " + sqlwhere + " ORDER BY WF_CC.CC_DATE DESC";

        String pageNO = StringTool.checkString(request.getParameter("pageNO"));

        String url = request.getContextPath() +
            "/system/flowlist.do?method=myencycliclist&key=" + key;

        try {
            SystemDataSource ds = SystemDataSource.getInstance();
            conn = ds.getConnection();

            DataTrimmer trimmer = new DataTrimmer(conn);

            String sql3 = "SELECT * FROM WF_READTRACE WHERE A_ID IN (" + sql2 +
                ") AND USER_ID = '" + user.id() + "'";
            Map map = trimmer.searchSymmetricalData(sql3, "A_ID", "A_ID");

            request.setAttribute("map", map);

            QueryJack plus = QueryJackFactory.getInstance().createQueryJack(false);

            // 设置
            plus.setParameter(QueryJackFactory.PAGINATION_SQL1, sql1);
            plus.setParameter(QueryJackFactory.PAGINATION_SQL2, sql2);
            plus.setParameter(QueryJackFactory.DATABASE_CONNECTION, conn);
            plus.setParameter(QueryJackFactory.PAGINATION_LINKSTRING, url);
            plus.setParameter(QueryJackFactory.PAGINATION_COUNT, "20");
            plus.setParameter(QueryJackFactory.PAGINATION_PAGENO, pageNO);

            // plus.setParameter(QueryJackFactory.PAGINATION_UPDATEPAGENO, "");
            List resultList = plus.work();

            if ((resultList != null) && !resultList.isEmpty()) {
                WInstanceManager manager = new WInstanceManager(conn);

                for (int i = 0; i < resultList.size(); i++) {
                    Map info = (Map) resultList.get(i);
                    String number = (String) info.get("PAGINATION_NUMBER");
                    String aid = (String) info.get("A_ID");
                    WActivity activity = manager.getActivityByID(aid);

                    resultList.set(i, new Object[] { number, activity });
                }
            }

            // 获得跳转控制
            Controller controller = plus.getController("form1");

            // 设置调转类型
            controller.setType("input");

            // 显示标签
            Label label = plus.getLabel();

            // 设置显示标签类型
            label.setType("href");

            Object[] result = new Object[4];
            result[0] = resultList;
            result[1] = label;
            result[2] = controller;
            result[3] = pageNO;

            request.setAttribute("result", result);

            Map query = new HashMap();

            query.put("qname", qname);
            query.put("qdate", DateTrimmer.getYMD_LBL(qdate));

            request.setAttribute("query", query);

            request.setAttribute("type", "encyclic");

            forward = mapping.findForward("myencycliclist");
        } catch (Exception ex) {
            forward = mapping.findForward("error");
            ISystem.catchException(request, this, ex);
        } finally {
            SystemDataSource.closeConnection(conn);
        }

        return forward;
    }

    /**
     * 我的传阅列表(return json)
     *
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward myencycliclist4json(ActionMapping mapping,
        ActionForm form, HttpServletRequest request,
        HttpServletResponse response) throws Exception {
        ActionForward forward = null;
        Connection conn = null;

        IUser user = ISystem.getSessionUser(request);

        String key = StringTool.checkString(request.getParameter("key"));

        request.setAttribute("key", key);

        SimpleBean parm = new CommonBean4HSQ(request);

        String qname = StringTool.checkString(parm.getResource().get("qname"));

        String qdate = StringTool.checkString(request.getParameter("qdate"));

        qdate = DateTrimmer.formatDateForVarchar(qdate);

        String flumeid = user.iallgroupflumeid(",");

        String groupIds = UserManager.iallgroupflumeid(flumeid);

        String sqlwhere = " ( ((WF_CC.OBJ_ID = '" + user.id() +
            "' AND OBJ_TYPE = 'user') OR (WF_CC.OBJ_ID IN (" + groupIds +
            ") AND OBJ_TYPE = 'group')) ";

        if (!qname.equals("")) {
            sqlwhere += (" AND WF_ACTIVITY.A_TITLE LIKE '%" + qname + "%' ");
        }

        if (!qdate.equals("")) {
            sqlwhere += (" AND WF_CC.CC_DATE LIKE '%" + qdate + "%' ");
        }

        if (!key.equals("")) {
            sqlwhere += (" AND WF_CC.WOBJECT = '" + key + "' ");
        }

        sqlwhere += ")";

        String sql1 =
            "SELECT COUNT(DISTINCT(WF_ACTIVITY.A_ID)) FROM WF_ACTIVITY,WF_CC WHERE WF_ACTIVITY.A_ID = " +
            "WF_CC.A_ID AND " + sqlwhere;
        String sql2 =
            "SELECT DISTINCT(WF_ACTIVITY.A_ID) AS A_ID FROM WF_ACTIVITY,WF_CC WHERE WF_ACTIVITY.A_ID = " +
            "WF_CC.A_ID AND " + sqlwhere + " ORDER BY WF_CC.CC_DATE DESC";

        String pageNO = StringTool.checkString(request.getParameter("pageNO"));

        String url = request.getContextPath() +
            "/system/flowlist.do?method=myencycliclist&key=" + key;

        try {
            SystemDataSource ds = SystemDataSource.getInstance();
            conn = ds.getConnection();

            QueryJack plus = QueryJackFactory.getInstance().createQueryJack(false);

            // 设置
            plus.setParameter(QueryJackFactory.PAGINATION_SQL1, sql1);
            plus.setParameter(QueryJackFactory.PAGINATION_SQL2, sql2);
            plus.setParameter(QueryJackFactory.DATABASE_CONNECTION, conn);
            plus.setParameter(QueryJackFactory.PAGINATION_LINKSTRING, url);
            plus.setParameter(QueryJackFactory.PAGINATION_COUNT, "20");
            plus.setParameter(QueryJackFactory.PAGINATION_PAGENO, pageNO);

            // plus.setParameter(QueryJackFactory.PAGINATION_UPDATEPAGENO, "");
            List resultList = plus.work();

            if ((resultList != null) && !resultList.isEmpty()) {
                WInstanceManager manager = new WInstanceManager(conn);

                for (int i = 0; i < resultList.size(); i++) {
                    Map info = (Map) resultList.get(i);
                    String aid = (String) info.get("A_ID");
                    WActivity act = manager.getActivityByID(aid);

                    info.put("A_TITLE", act.ititle());
                    info.put("S_NAME", act.istep().iname());
                    info.put("A_STATE", act.istate());
                    info.put("A_STIME", act.istarttime());
                    info.put("A_LTIME", act.ilmtime());
                    info.put("INS_OBJECT",
                        act.instance().iflow().iobject().iname());
                }
            }

            // 获得跳转控制
            Controller controller = plus.getController("form1");

            // 设置调转类型
            controller.setType("input");

            // 显示标签
            Label label = plus.getLabel();

            // 设置显示标签类型
            label.setType("href");

            Object[] result = new Object[4];
            result[0] = resultList;
            result[1] = label;
            result[2] = controller;
            result[3] = pageNO;

            ListRange lr = new ListRange();
            lr.setTotalSize(label.totaldata());
            lr.setList(resultList);
            lr.setSuccess(true);
            outJson(lr, response);
        } catch (Exception ex) {
            forward = mapping.findForward("error");
            ISystem.catchException(request, this, ex);
        } finally {
            SystemDataSource.closeConnection(conn);
        }

        return forward;
    }

    /**
     * 传阅列表
     *
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward encycliclist(ActionMapping mapping, ActionForm form,
        HttpServletRequest request, HttpServletResponse response)
        throws Exception {
        ActionForward forward = null;
        Connection conn = null;

        String key = StringTool.checkString(request.getParameter("key"));

        IUser user = ISystem.getSessionUser(request);

        Map query = new HashMap();

        CommonBean4HSQ cbh = new CommonBean4HSQ(request);

        Map param = cbh.getResource();

        /*标题*/
        String qname = StringTool.checkString(param.get("qname"));

        /*接收日期*/
        String qdate = StringTool.checkString(request.getParameter("qdate"));

        /*起草人所在部门*/
        String qunit = StringTool.checkString(param.get("qunit"));

        /*起草人*/
        String qcreator = StringTool.checkString(param.get("qcreator"));

        /*起草日期*/
        String qcreatedate = StringTool.checkString(request.getParameter(
                    "qcreatedate"));

        qdate = DateTrimmer.formatDateForVarchar(qdate);

        qcreatedate = DateTrimmer.formatDateForVarchar(qcreatedate);

        String flumeid = user.iallgroupflumeid(",");

        String groupIds = UserManager.iallgroupflumeid(flumeid);

        String sqlwhere = " ( ((WF_CC.OBJ_ID = '" + user.id() +
            "' AND OBJ_TYPE = 'user') OR (WF_CC.OBJ_ID IN (" + groupIds +
            ") AND OBJ_TYPE = 'group')) ";

        if (!qname.equals("")) {
            sqlwhere += (" AND WF_ACTIVITY.A_TITLE LIKE '%" + qname + "%' ");
        }

        if (!qdate.equals("")) {
            sqlwhere += (" AND WF_CC.CC_DATE LIKE '%" + qdate + "%' ");
        }

        if (!key.equals("")) {
            sqlwhere += (" AND WF_CC.WOBJECT = '" + key + "' ");
        }

        sqlwhere += ")";

        String sql1 =
            "SELECT COUNT(DISTINCT(WF_ACTIVITY.A_ID)) FROM WF_ACTIVITY,WF_CC WHERE WF_ACTIVITY.A_ID = " +
            "WF_CC.A_ID AND " + sqlwhere;
        String sql2 =
            "SELECT DISTINCT(WF_ACTIVITY.A_ID) AS A_ID FROM WF_ACTIVITY,WF_CC WHERE WF_ACTIVITY.A_ID = " +
            "WF_CC.A_ID AND " + sqlwhere + " ORDER BY WF_CC.CC_DATE DESC";

        String pageNO = StringTool.checkString(request.getParameter("pageNO"));

        String url = request.getContextPath() +
            "/system/flowlist.do?method=encycliclist&key=" + key;

        try {
            WObjectRegister register = WObjectRegister.getInstance();

            WObject object = register.findWObject(key);

            SystemDataSource ds = SystemDataSource.getInstance();
            conn = ds.getConnection();

            DataTrimmer trimmer = new DataTrimmer(conn);

            String sql3 = "SELECT * FROM WF_READTRACE WHERE A_ID IN (" + sql2 +
                ") AND USER_ID = '" + user.id() + "'";
            Map map = trimmer.searchSymmetricalData(sql3, "A_ID", "A_ID");

            request.setAttribute("map", map);

            QueryJack plus = QueryJackFactory.getInstance().createQueryJack(false);

            // 设置
            plus.setParameter(QueryJackFactory.PAGINATION_SQL1, sql1);
            plus.setParameter(QueryJackFactory.PAGINATION_SQL2, sql2);
            plus.setParameter(QueryJackFactory.DATABASE_CONNECTION, conn);
            plus.setParameter(QueryJackFactory.PAGINATION_LINKSTRING, url);
            plus.setParameter(QueryJackFactory.PAGINATION_COUNT, "20");
            plus.setParameter(QueryJackFactory.PAGINATION_PAGENO, pageNO);

            // plus.setParameter(QueryJackFactory.PAGINATION_UPDATEPAGENO, "");
            List resultList = plus.work();

            WorkflowManager manager = new WorkflowManager(conn);

            if ((resultList != null) && !resultList.isEmpty()) {
                WInstanceManager imanager = new WInstanceManager(conn);

                for (int i = 0; i < resultList.size(); i++) {
                    Map info = (Map) resultList.get(i);
                    String number = (String) info.get("PAGINATION_NUMBER");
                    String aid = (String) info.get("A_ID");
                    WActivity activity = imanager.getActivityByID(aid);

                    resultList.set(i, new Object[] { number, activity });
                }
            }

            // 获得跳转控制
            Controller controller = plus.getController("form1");

            // 设置调转类型
            controller.setType("input");

            // 显示标签
            Label label = plus.getLabel();

            // 设置显示标签类型
            label.setType("href");

            Object[] result = new Object[4];
            result[0] = resultList;
            result[1] = label;
            result[2] = controller;
            result[3] = pageNO;

            request.setAttribute("result", result);

            request.setAttribute("object", object);

            request.setAttribute("query", query);

            request.setAttribute("type", "encyclic");

            forward = new ActionForward();
            forward.setPath(object.iview4encyclic());
        } catch (Exception ex) {
            forward = mapping.findForward("error");
            ISystem.catchException(request, this, ex);
        } finally {
            SystemDataSource.closeConnection(conn);
        }

        return forward;
    }

    /**
     * 我的待办列表
     *
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward mypendinglist(ActionMapping mapping, ActionForm form,
        HttpServletRequest request, HttpServletResponse response)
        throws Exception {
        ActionForward forward = null;
        Connection conn = null;

        IUser user = ISystem.getSessionUser(request);

        String key = StringTool.checkString(request.getParameter("key"));

        request.setAttribute("key", key);

        SimpleBean parm = new CommonBean4HSQ(request);

        String qname = StringTool.checkString(parm.getResource().get("qname"));

        String qdate = StringTool.checkString(request.getParameter("qdate"));

        qdate = DateTrimmer.formatDateForVarchar(qdate);

        String sqlwhere =
            " WHERE (WF_ACTIVITY.A_STATE = 0 OR WF_ACTIVITY.A_STATE = 1 OR WF_ACTIVITY.A_STATE = -1) " +
            "AND WF_ACTIVITY.A_TYPE = 0 AND WF_INSTANCE.INS_STATE = 0 AND A.USER_ID = '" +
            user.id() + "'";

        if (!qname.equals("")) {
            sqlwhere += (" AND A_TITLE LIKE '%" + qname + "%' ");
        }

        if (!qdate.equals("")) {
            sqlwhere += (" AND A_STIME LIKE '%" + qdate + "%' ");
        }

        if (!key.equals("")) {
            sqlwhere += (" AND WF_INSTANCE.INS_OBJECT = '" + key + "'");
        }

        String sql1 =
            "SELECT COUNT(WF_ACTIVITY.A_ID) FROM WF_ACTIVITY LEFT JOIN WF_INSTANCE ON WF_ACTIVITY.INS_ID = " +
            "WF_INSTANCE.INS_ID LEFT JOIN WF_EXECUTOR A ON WF_ACTIVITY.A_ID = A.A_ID " +
            sqlwhere;
        String sql2 =
            "SELECT WF_ACTIVITY.*,WF_INSTANCE.*,B.* FROM WF_ACTIVITY LEFT JOIN WF_INSTANCE ON WF_ACTIVITY.INS_ID = " +
            "WF_INSTANCE.INS_ID LEFT JOIN WF_EXECUTOR A ON WF_ACTIVITY.A_ID = A.A_ID LEFT JOIN WF_EXECUTOR B ON WF_ACTIVITY.A_LASTID = B.A_ID " +
            sqlwhere + " ORDER BY A_STIME DESC";

        String pageNO = StringTool.checkString(request.getParameter("pageNO"));

        String url = request.getContextPath() +
            "/system/flowlist.do?method=mypendinglist&key=" + key;

        try {
            SystemDataSource ds = SystemDataSource.getInstance();
            conn = ds.getConnection();

            QueryJack plus = QueryJackFactory.getInstance().createQueryJack(false);

            // 设置
            plus.setParameter(QueryJackFactory.PAGINATION_SQL1, sql1);
            plus.setParameter(QueryJackFactory.PAGINATION_SQL2, sql2);
            plus.setParameter(QueryJackFactory.DATABASE_CONNECTION, conn);
            plus.setParameter(QueryJackFactory.PAGINATION_LINKSTRING, url);
            plus.setParameter(QueryJackFactory.PAGINATION_COUNT, "20");
            plus.setParameter(QueryJackFactory.PAGINATION_PAGENO, pageNO);

            // plus.setParameter(QueryJackFactory.PAGINATION_UPDATEPAGENO, "");
            List resultList = plus.work();

            if ((resultList != null) && !resultList.isEmpty()) {
                Map objmap = WObjectRegister.getInstance().getAllWObjectMap();
                request.setAttribute("objmap", objmap);
            }

            // 获得跳转控制
            Controller controller = plus.getController("form1");

            // 设置调转类型
            controller.setType("input");

            // 显示标签
            Label label = plus.getLabel();

            // 设置显示标签类型
            label.setType("href");

            Object[] result = new Object[4];
            result[0] = resultList;
            result[1] = label;
            result[2] = controller;
            result[3] = pageNO;

            request.setAttribute("result", result);

            Map query = new HashMap();

            query.put("qname", qname);
            query.put("qdate", DateTrimmer.getYMD_LBL(qdate));

            request.setAttribute("query", query);

            request.setAttribute("type", "pending");

            forward = mapping.findForward("mypendinglist");
        } catch (Exception ex) {
            forward = mapping.findForward("error");
            ISystem.catchException(request, this, ex);
        } finally {
            SystemDataSource.closeConnection(conn);
        }

        return forward;
    }

    /**
     * 待办列表
     *
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward pendinglist(ActionMapping mapping, ActionForm form,
        HttpServletRequest request, HttpServletResponse response)
        throws Exception {
        ActionForward forward = null;
        Connection conn = null;

        IUser user = ISystem.getSessionUser(request);

        String key = StringTool.checkString(request.getParameter("key"));

        request.setAttribute("key", key);

        SimpleBean parm = new CommonBean4HSQ(request);

        String qname = StringTool.checkString(parm.getResource().get("qname"));

        String qdate = StringTool.checkString(request.getParameter("qdate"));

        qdate = DateTrimmer.formatDateForVarchar(qdate);

        String sqlwhere =
            " WHERE (WF_ACTIVITY.A_STATE = 0 OR WF_ACTIVITY.A_STATE = 1 OR WF_ACTIVITY.A_STATE = -1) " +
            "AND WF_ACTIVITY.A_TYPE = 0 AND WF_INSTANCE.INS_STATE = 0 AND A.USER_ID = '" +
            user.id() + "'";

        if (!qname.equals("")) {
            sqlwhere += (" AND A_TITLE LIKE '%" + qname + "%' ");
        }

        if (!qdate.equals("")) {
            sqlwhere += (" AND A_STIME LIKE '%" + qdate + "%' ");
        }

        if (!key.equals("")) {
            sqlwhere += (" AND WF_INSTANCE.INS_OBJECT = '" + key + "'");
        }

        String sql1 =
            "SELECT COUNT(WF_ACTIVITY.A_ID) FROM WF_ACTIVITY LEFT JOIN WF_INSTANCE ON WF_ACTIVITY.INS_ID = " +
            "WF_INSTANCE.INS_ID LEFT JOIN WF_EXECUTOR A ON WF_ACTIVITY.A_ID = A.A_ID " +
            sqlwhere;
        String sql2 =
            "SELECT WF_ACTIVITY.*,WF_INSTANCE.*,B.* FROM WF_ACTIVITY LEFT JOIN WF_INSTANCE ON WF_ACTIVITY.INS_ID = " +
            "WF_INSTANCE.INS_ID LEFT JOIN WF_EXECUTOR A ON WF_ACTIVITY.A_ID = A.A_ID LEFT JOIN WF_EXECUTOR B ON WF_ACTIVITY.A_LASTID = B.A_ID " +
            sqlwhere + " ORDER BY A_STIME DESC";

        String pageNO = StringTool.checkString(request.getParameter("pageNO"));

        String url = request.getContextPath() +
            "/system/flowlist.do?method=pendinglist&key=" + key;

        try {
            SystemDataSource ds = SystemDataSource.getInstance();
            conn = ds.getConnection();

            QueryJack plus = QueryJackFactory.getInstance().createQueryJack(false);

            // 设置
            plus.setParameter(QueryJackFactory.PAGINATION_SQL1, sql1);
            plus.setParameter(QueryJackFactory.PAGINATION_SQL2, sql2);
            plus.setParameter(QueryJackFactory.DATABASE_CONNECTION, conn);
            plus.setParameter(QueryJackFactory.PAGINATION_LINKSTRING, url);
            plus.setParameter(QueryJackFactory.PAGINATION_COUNT, "20");
            plus.setParameter(QueryJackFactory.PAGINATION_PAGENO, pageNO);

            // plus.setParameter(QueryJackFactory.PAGINATION_UPDATEPAGENO, "");
            List resultList = plus.work();

            if ((resultList != null) && !resultList.isEmpty()) {
                Map objmap = WObjectRegister.getInstance().getAllWObjectMap();
                request.setAttribute("objmap", objmap);
            }

            // 获得跳转控制
            Controller controller = plus.getController("form1");

            // 设置调转类型
            controller.setType("input");

            // 显示标签
            Label label = plus.getLabel();

            // 设置显示标签类型
            label.setType("href");

            Object[] result = new Object[4];
            result[0] = resultList;
            result[1] = label;
            result[2] = controller;
            result[3] = pageNO;

            request.setAttribute("result", result);

            Map query = new HashMap();

            query.put("qname", qname);
            query.put("qdate", DateTrimmer.getYMD_LBL(qdate));

            request.setAttribute("query", query);

            request.setAttribute("type", "pending");

            forward = mapping.findForward("mypendinglist");
        } catch (Exception ex) {
            forward = mapping.findForward("error");
            ISystem.catchException(request, this, ex);
        } finally {
            SystemDataSource.closeConnection(conn);
        }

        return forward;
    }

    /**
     * 待办列表(返回json数据)
     *
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward pendinglist4Json(ActionMapping mapping,
        ActionForm form, HttpServletRequest request,
        HttpServletResponse response) throws Exception {
        ActionForward forward = null;
        Connection conn = null;

        IUser user = ISystem.getSessionUser(request);

        String key = StringTool.checkString(request.getParameter("key"));

        request.setAttribute("key", key);

        SimpleBean parm = new CommonBean4HSQ(request);

        String size = StringTool.checkString(parm.getResource().get("size"));

        if (size.equals("")) {
            size = "20";
        }

        String qname = StringTool.checkString(parm.getResource().get("qname"));

        String qdate = StringTool.checkString(request.getParameter("qdate"));

        qdate = DateTrimmer.formatDateForVarchar(qdate);

        String sqlwhere =
            " WHERE (WF_ACTIVITY.A_STATE = 0 OR WF_ACTIVITY.A_STATE = 1 OR WF_ACTIVITY.A_STATE = -1) " +
            "AND WF_ACTIVITY.A_TYPE = 0 AND WF_INSTANCE.INS_STATE = 0 AND A.USER_ID = '" +
            user.id() + "'";

        if (!qname.equals("")) {
            sqlwhere += (" AND A_TITLE LIKE '%" + qname + "%' ");
        }

        if (!qdate.equals("")) {
            sqlwhere += (" AND A_STIME LIKE '%" + qdate + "%' ");
        }

        if (!key.equals("")) {
            sqlwhere += (" AND WF_INSTANCE.INS_OBJECT = '" + key + "'");
        }

        String sql1 =
            "SELECT COUNT(WF_ACTIVITY.A_ID) FROM WF_ACTIVITY LEFT JOIN WF_INSTANCE ON WF_ACTIVITY.INS_ID = " +
            "WF_INSTANCE.INS_ID LEFT JOIN WF_EXECUTOR A ON WF_ACTIVITY.A_ID = A.A_ID " +
            sqlwhere;
        String sql2 =
            "SELECT WF_ACTIVITY.A_ID FROM WF_ACTIVITY LEFT JOIN WF_INSTANCE ON WF_ACTIVITY.INS_ID = " +
            "WF_INSTANCE.INS_ID LEFT JOIN WF_EXECUTOR A ON WF_ACTIVITY.A_ID = A.A_ID LEFT JOIN WF_EXECUTOR B ON WF_ACTIVITY.A_LASTID = B.A_ID " +
            sqlwhere + " ORDER BY A_STIME DESC";

        String pageNO = StringTool.checkString(request.getParameter("pageNO"));

        String url = request.getContextPath() +
            "/system/flowlist.do?method=pendinglist&key=" + key;

        try {
            SystemDataSource ds = SystemDataSource.getInstance();
            conn = ds.getConnection();

            QueryJack plus = QueryJackFactory.getInstance().createQueryJack(false);

            // 设置
            plus.setParameter(QueryJackFactory.PAGINATION_SQL1, sql1);
            plus.setParameter(QueryJackFactory.PAGINATION_SQL2, sql2);
            plus.setParameter(QueryJackFactory.DATABASE_CONNECTION, conn);
            plus.setParameter(QueryJackFactory.PAGINATION_LINKSTRING, url);
            plus.setParameter(QueryJackFactory.PAGINATION_COUNT, size);
            plus.setParameter(QueryJackFactory.PAGINATION_PAGENO, pageNO);

            // plus.setParameter(QueryJackFactory.PAGINATION_UPDATEPAGENO, "");
            List resultList = plus.work();

            if ((resultList != null) && !resultList.isEmpty()) {
                WInstanceManager im = new WInstanceManager(conn);

                for (int i = 0; i < resultList.size(); i++) {
                    Map info = (Map) resultList.get(i);
                    String id = (String) info.get("A_ID");

                    WActivity act = im.getActivityByID(id);

                    info.put("A_TITLE", act.ititle());
                    info.put("S_NAME", act.istep().iname());
                    info.put("A_STATE", act.istate());
                    info.put("A_STIME", act.istarttime());
                    info.put("A_LTIME", act.ilmtime());
                    info.put("INS_OBJECT",
                        act.instance().iflow().iobject().iname());
                }
            }

            // 获得跳转控制
            Controller controller = plus.getController("form1");

            // 设置调转类型
            controller.setType("input");

            // 显示标签
            Label label = plus.getLabel();

            // 设置显示标签类型
            label.setType("href");

            Object[] result = new Object[4];
            result[0] = resultList;
            result[1] = label;
            result[2] = controller;
            result[3] = pageNO;

            request.setAttribute("result", result);

            Map query = new HashMap();

            query.put("qname", qname);
            query.put("qdate", DateTrimmer.getYMD_LBL(qdate));

            request.setAttribute("query", query);

            request.setAttribute("type", "pending");

            ListRange lr = new ListRange();
            lr.setTotalSize(label.totaldata());
            lr.setList(resultList);
            lr.setSuccess(true);
            outJson(lr, response);
        } catch (Exception ex) {
            forward = mapping.findForward("error");
            ISystem.catchException(request, this, ex);
        } finally {
            SystemDataSource.closeConnection(conn);
        }

        return forward;
    }

    /**
     * 我的已办列表
     *
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward mydonelist(ActionMapping mapping, ActionForm form,
        HttpServletRequest request, HttpServletResponse response)
        throws Exception {
        ActionForward forward = null;
        Connection conn = null;

        String key = StringTool.checkString(request.getParameter("key"));

        request.setAttribute("key", key);

        SimpleBean parm = new CommonBean4HSQ(request);

        String qname = StringTool.checkString(parm.getResource().get("qname"));

        String qdate = StringTool.checkString(request.getParameter("qdate"));

        qdate = DateTrimmer.formatDateForVarchar(qdate);

        String sqlwhere = "";

        if (!qname.equals("")) {
            sqlwhere += (" AND A_TITLE LIKE '%" + qname + "%' ");
        }

        if (!qdate.equals("")) {
            sqlwhere += (" AND A_ETIME LIKE '%" + qdate + "%' ");
        }

        if (!key.equals("")) {
            sqlwhere += (" AND WF_INSTANCE.INS_OBJECT = '" + key + "'");
        }

        IUser user = ISystem.getSessionUser(request);

        String sql1 =
            "SELECT COUNT(A_ID) FROM (SELECT MAX(WF_ACTIVITY.A_ID) AS A_ID FROM WF_ACTIVITY,WF_EXECUTOR,WF_INSTANCE WHERE WF_ACTIVITY.A_ID = " +
            "WF_EXECUTOR.A_ID AND WF_ACTIVITY.A_TYPE = 0 AND WF_ACTIVITY.A_STATE = 2 AND WF_EXECUTOR.USER_ID = '" +
            user.id() + "' AND WF_ACTIVITY.INS_ID = WF_INSTANCE.INS_ID " +
            sqlwhere + " GROUP BY WF_ACTIVITY.INS_ID) A";
        String sql2 =
            "SELECT MAX(WF_ACTIVITY.A_ID) AS A_ID FROM WF_ACTIVITY,WF_EXECUTOR,WF_INSTANCE WHERE WF_ACTIVITY.A_ID = " +
            "WF_EXECUTOR.A_ID AND WF_ACTIVITY.A_TYPE = 0 AND WF_ACTIVITY.A_STATE = 2 AND WF_EXECUTOR.USER_ID = '" +
            user.id() + "' AND WF_ACTIVITY.INS_ID = WF_INSTANCE.INS_ID " +
            sqlwhere +
            " GROUP BY WF_ACTIVITY.INS_ID ORDER BY WF_ACTIVITY.A_ETIME DESC";

        String pageNO = StringTool.checkString(request.getParameter("pageNO"));

        String url = request.getContextPath() +
            "/system/flowlist.do?method=mydonelist&key=" + key;

        try {
            SystemDataSource ds = SystemDataSource.getInstance();
            conn = ds.getConnection();

            QueryJack plus = QueryJackFactory.getInstance().createQueryJack(false);

            // 设置
            plus.setParameter(QueryJackFactory.PAGINATION_SQL1, sql1);
            plus.setParameter(QueryJackFactory.PAGINATION_SQL2, sql2);
            plus.setParameter(QueryJackFactory.DATABASE_CONNECTION, conn);
            plus.setParameter(QueryJackFactory.PAGINATION_LINKSTRING, url);
            plus.setParameter(QueryJackFactory.PAGINATION_COUNT, "20");
            plus.setParameter(QueryJackFactory.PAGINATION_PAGENO, pageNO);

            // plus.setParameter(QueryJackFactory.PAGINATION_UPDATEPAGENO, "");
            List resultList = plus.work();

            if ((resultList != null) && !resultList.isEmpty()) {
                WInstanceManager manager = new WInstanceManager(conn);

                for (int i = 0; i < resultList.size(); i++) {
                    Map info = (Map) resultList.get(i);
                    String number = (String) info.get("PAGINATION_NUMBER");
                    String aid = (String) info.get("A_ID");
                    WActivity activity = manager.getActivityByID(aid);

                    resultList.set(i, new Object[] { number, activity });
                }
            }

            // 获得跳转控制
            Controller controller = plus.getController("form1");

            // 设置调转类型
            controller.setType("input");

            // 显示标签
            Label label = plus.getLabel();

            // 设置显示标签类型
            label.setType("href");

            Object[] result = new Object[4];
            result[0] = resultList;
            result[1] = label;
            result[2] = controller;
            result[3] = pageNO;

            request.setAttribute("result", result);

            Map query = new HashMap();

            query.put("qname", qname);
            query.put("qdate", DateTrimmer.getYMD_LBL(qdate));

            request.setAttribute("query", query);

            request.setAttribute("type", "done");

            forward = mapping.findForward("mydonelist");
        } catch (Exception ex) {
            forward = mapping.findForward("error");
            ISystem.catchException(request, this, ex);
        } finally {
            SystemDataSource.closeConnection(conn);
        }

        return forward;
    }

    /**
     * 已办列表
     *
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward donelist(ActionMapping mapping, ActionForm form,
        HttpServletRequest request, HttpServletResponse response)
        throws Exception {
        ActionForward forward = null;
        Connection conn = null;

        String key = StringTool.checkString(request.getParameter("key"));

        IUser user = ISystem.getSessionUser(request);

        String pageNO = StringTool.checkString(request.getParameter("pageNO"));

        Map query = new HashMap();

        SimpleBean cbh = new CommonBean4HSQ(request);

        Map param = cbh.getResource();

        /*标题*/
        String qname = StringTool.checkString(param.get("qname"));

        /*接收日期*/
        String qdate = StringTool.checkString(request.getParameter("qdate"));

        /*起草人所在部门*/
        String qunit = StringTool.checkString(param.get("qunit"));

        /*起草人*/
        String qcreator = StringTool.checkString(param.get("qcreator"));

        /*起草日期*/
        String qcreatedate = StringTool.checkString(request.getParameter(
                    "qcreatedate"));

        qdate = DateTrimmer.formatDateForVarchar(qdate);

        qcreatedate = DateTrimmer.formatDateForVarchar(qcreatedate);

        String url = request.getContextPath() +
            "/system/flowlist.do?method=donelist&key=" + key;

        try {
            WObjectRegister register = WObjectRegister.getInstance();

            WObject object = register.findWObject(key);

            SystemDataSource ds = SystemDataSource.getInstance();
            conn = ds.getConnection();

            String sqlwhere = " ( 1=1 ";

            if (!qname.equals("")) {
                sqlwhere += (" AND A_TITLE LIKE '%" + qname + "%' ");
                query.put("qname", qname);
            }

            if (!qdate.equals("")) {
                sqlwhere += (" AND A_ETIME LIKE '%" + qdate + "%' ");
                query.put("qdate", DateTrimmer.getYMD_S_LBL(qdate));
            }

            sqlwhere += ")";

            String sqlin =
                "SELECT WF_ACTIVITY.INS_ID FROM WF_ACTIVITY,WF_CATEGORY,WF_INSTANCE,WF_EXECUTOR,WF_STEP WHERE " +
                "WF_ACTIVITY.INS_ID = WF_INSTANCE.INS_ID AND WF_INSTANCE.INS_STATE = 0 AND WF_INSTANCE.CG_ID = WF_CATEGORY.CG_ID AND WF_CATEGORY.OBJ_NAME = '" +
                key + "' AND " +
                "WF_ACTIVITY.A_ID = WF_EXECUTOR.A_ID AND WF_ACTIVITY.S_ID = WF_STEP.S_ID AND WF_STEP.S_TYPE <> 2 AND " +
                "WF_ACTIVITY.A_STATE = 2 AND WF_EXECUTOR.USER_ID = '" +
                user.id() + "'";

            if (!qunit.equals("")) {
                sqlin += (" AND INS_CUNITNAME LIKE '%" + qunit + "%'");
                query.put("qunit", qunit);
            }

            if (!qcreator.equals("")) {
                sqlin += (" AND INS_CREATORNAME LIKE '%" + qcreator + "%'");
                query.put("qcreator", qcreator);
            }

            if (!qcreatedate.equals("")) {
                sqlin += (" AND INS_START LIKE '%" + qcreatedate + "%'");
                query.put("qcreatedate", DateTrimmer.getYMD_S_LBL(qcreatedate));
            }

            sqlin += " GROUP BY WF_ACTIVITY.INS_ID";

            String sql1 =
                "SELECT COUNT(A_ID) FROM WF_ACTIVITY WHERE A_ID IN (SELECT MAX(WF_ACTIVITY.A_ID) FROM WF_ACTIVITY,WF_EXECUTOR,WF_STEP WHERE WF_ACTIVITY.A_ID = " +
                "WF_EXECUTOR.A_ID AND WF_ACTIVITY.S_ID = WF_STEP.S_ID AND WF_STEP.S_TYPE <> 2 AND " +
                "WF_ACTIVITY.A_STATE = 2 AND WF_EXECUTOR.USER_ID = '" +
                user.id() + "' AND WF_ACTIVITY.INS_ID IN (" + sqlin + ") AND " +
                sqlwhere + " GROUP BY INS_ID)";
            String sql2 =
                "SELECT MAX(WF_ACTIVITY.A_ID) AS A_ID FROM WF_ACTIVITY,WF_EXECUTOR,WF_STEP WHERE WF_ACTIVITY.A_ID = " +
                "WF_EXECUTOR.A_ID AND WF_ACTIVITY.S_ID = WF_STEP.S_ID AND WF_STEP.S_TYPE <> 2 AND " +
                "WF_ACTIVITY.A_STATE = 2 AND WF_EXECUTOR.USER_ID = '" +
                user.id() + "' AND WF_ACTIVITY.INS_ID IN (" + sqlin + ") AND " +
                sqlwhere + " GROUP BY INS_ID ORDER BY A_ID DESC";

            QueryJack plus = QueryJackFactory.getInstance().createQueryJack(false);

            // 设置
            plus.setParameter(QueryJackFactory.PAGINATION_SQL1, sql1);
            plus.setParameter(QueryJackFactory.PAGINATION_SQL2, sql2);
            plus.setParameter(QueryJackFactory.DATABASE_CONNECTION, conn);
            plus.setParameter(QueryJackFactory.PAGINATION_LINKSTRING, url);
            plus.setParameter(QueryJackFactory.PAGINATION_COUNT, "20");
            plus.setParameter(QueryJackFactory.PAGINATION_PAGENO, pageNO);

            // plus.setParameter(QueryJackFactory.PAGINATION_UPDATEPAGENO, "");
            List resultList = plus.work();

            WorkflowManager manager = new WorkflowManager(conn);

            if ((resultList != null) && !resultList.isEmpty()) {
                WInstanceManager imanager = new WInstanceManager(conn);

                for (int i = 0; i < resultList.size(); i++) {
                    Map info = (Map) resultList.get(i);
                    String number = (String) info.get("PAGINATION_NUMBER");
                    String aid = (String) info.get("A_ID");
                    WActivity activity = imanager.getActivityByID(aid);

                    resultList.set(i, new Object[] { number, activity });
                }
            }

            // 获得跳转控制
            Controller controller = plus.getController("form1");

            // 设置调转类型
            controller.setType("input");

            // 显示标签
            Label label = plus.getLabel();

            // 设置显示标签类型
            label.setType("href");

            Object[] result = new Object[4];
            result[0] = resultList;
            result[1] = label;
            result[2] = controller;
            result[3] = pageNO;

            request.setAttribute("result", result);

            request.setAttribute("object", object);

            request.setAttribute("query", query);

            request.setAttribute("type", "done");

            forward = new ActionForward();
            forward.setPath(object.iview4done());
        } catch (Exception ex) {
            forward = mapping.findForward("error");
            ISystem.catchException(request, this, ex);
        } finally {
            SystemDataSource.closeConnection(conn);
        }

        return forward;
    }

    /**
     * 我的抄送列表
     *
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward mycclist(ActionMapping mapping, ActionForm form,
        HttpServletRequest request, HttpServletResponse response)
        throws Exception {
        ActionForward forward = null;
        Connection conn = null;

        IUser user = ISystem.getSessionUser(request);

        String key = StringTool.checkString(request.getParameter("key"));

        request.setAttribute("key", key);

        SimpleBean parm = new CommonBean4HSQ(request);

        String qname = StringTool.checkString(parm.getResource().get("qname"));

        String qdate = StringTool.checkString(request.getParameter("qdate"));

        qdate = DateTrimmer.formatDateForVarchar(qdate);

        String sqlwhere = " ( 1=1 ";

        if (!qname.equals("")) {
            sqlwhere += (" AND A_TITLE LIKE '%" + qname + "%' ");
        }

        if (!qdate.equals("")) {
            sqlwhere += (" AND CC_DATE LIKE '%" + qdate + "%' ");
        }

        if (!key.equals("")) {
            sqlwhere += (" AND WF_ACTIVITY.INS_ID IN (SELECT INS_ID FROM WF_INSTANCE,WF_CATEGORY WHERE WF_INSTANCE.CG_ID = WF_CATEGORY.CG_ID AND WF_CATEGORY.OBJ_NAME = '" +
            key + "')");
        }

        sqlwhere += ")";

        String sql1 =
            "SELECT A_ID,CC_DATE FROM WF_CC WHERE  OBJ_TYPE = 'u' AND OBJ_ID = '" +
            user.id() + "'";

        String sql2 =
            "SELECT A_ID,CC_DATE FROM WF_CC WHERE OBJ_TYPE = 'u' AND OBJ_ID = '" +
            user.id() + "'";

        if (user.igroup() != null) {
            String lumeid = UserManager.iallgroupflumeid(",");

            String sqlunion =
                " UNION SELECT A_ID,CC_DATE FROM WF_CC WHERE OBJ_TYPE = 'g' AND OBJ_ID in (" +
                lumeid + ")";

            sql1 = "SELECT COUNT(A.A_ID) FROM (" + sql1 + sqlunion +
                ") A, WF_ACTIVITY WHERE A.A_ID = WF_ACTIVITY.A_ID AND " +
                sqlwhere;

            sql2 = "SELECT A.A_ID,A.CC_DATE FROM (" + sql2 + sqlunion +
                ") A, WF_ACTIVITY WHERE A.A_ID = WF_ACTIVITY.A_ID AND " +
                sqlwhere + " ORDER BY A_STIME DESC";
        } else {
            sql1 = "SELECT COUNT(*) FROM (" + sql1 +
                ") A, WF_ACTIVITY WHERE A.A_ID = WF_ACTIVITY.A_ID AND " +
                sqlwhere;
            sql2 = "SELECT A.A_ID,A.CC_DATE FROM (" + sql2 +
                ") A, WF_ACTIVITY WHERE A.A_ID = WF_ACTIVITY.A_ID AND " +
                sqlwhere + " ORDER BY CC_DATE DESC";
        }

        String pageNO = StringTool.checkString(request.getParameter("pageNO"));

        String url = request.getContextPath() +
            "/system/flowlist.do?method=mycclist&key=" + key;

        try {
            SystemDataSource ds = SystemDataSource.getInstance();
            conn = ds.getConnection();

            QueryJack plus = QueryJackFactory.getInstance().createQueryJack(false);

            // 设置
            plus.setParameter(QueryJackFactory.PAGINATION_SQL1, sql1);
            plus.setParameter(QueryJackFactory.PAGINATION_SQL2, sql2);
            plus.setParameter(QueryJackFactory.DATABASE_CONNECTION, conn);
            plus.setParameter(QueryJackFactory.PAGINATION_LINKSTRING, url);
            plus.setParameter(QueryJackFactory.PAGINATION_COUNT, "20");
            plus.setParameter(QueryJackFactory.PAGINATION_PAGENO, pageNO);

            // plus.setParameter(QueryJackFactory.PAGINATION_UPDATEPAGENO, "");
            List resultList = plus.work();

            if ((resultList != null) && !resultList.isEmpty()) {
                WInstanceManager manager = new WInstanceManager(conn);

                for (int i = 0; i < resultList.size(); i++) {
                    Map info = (Map) resultList.get(i);
                    String number = (String) info.get("PAGINATION_NUMBER");
                    String aid = (String) info.get("A_ID");
                    String date = (String) info.get("CC_DATE");
                    WActivity activity = manager.getActivityByID(aid);

                    resultList.set(i, new Object[] { number, activity, date });
                }
            }

            // 获得跳转控制
            Controller controller = plus.getController("form1");

            // 设置调转类型
            controller.setType("input");

            // 显示标签
            Label label = plus.getLabel();

            // 设置显示标签类型
            label.setType("href");

            Object[] result = new Object[4];
            result[0] = resultList;
            result[1] = label;
            result[2] = controller;
            result[3] = pageNO;

            request.setAttribute("result", result);

            Map query = new HashMap();

            query.put("qname", qname);
            query.put("qdate", DateTrimmer.getYMD_LBL(qdate));

            request.setAttribute("query", query);

            request.setAttribute("type", "cc");

            forward = mapping.findForward("mycclist");
        } catch (Exception ex) {
            forward = mapping.findForward("error");
            ISystem.catchException(request, this, ex);
        } finally {
            SystemDataSource.closeConnection(conn);
        }

        return forward;
    }

    /**
     * 抄送列表
     *
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward cclist(ActionMapping mapping, ActionForm form,
        HttpServletRequest request, HttpServletResponse response)
        throws Exception {
        ActionForward forward = null;
        Connection conn = null;

        String key = StringTool.checkString(request.getParameter("key"));

        IUser user = ISystem.getSessionUser(request);

        SimpleBean cbh = new CommonBean4HSQ(request);

        Map param = cbh.getResource();

        Map query = new HashMap();

        /*标题*/
        String qname = StringTool.checkString(param.get("qname"));

        /*接收日期*/
        String qdate = StringTool.checkString(request.getParameter("qdate"));

        /*起草人所在部门*/
        String qunit = StringTool.checkString(param.get("qunit"));

        /*起草人*/
        String qcreator = StringTool.checkString(param.get("qcreator"));

        /*起草日期*/
        String qcreatedate = StringTool.checkString(request.getParameter(
                    "qcreatedate"));

        qdate = DateTrimmer.formatDateForVarchar(qdate);

        qcreatedate = DateTrimmer.formatDateForVarchar(qcreatedate);

        /*活动记录标题*/
        String sqlwhere = " ( 1=1 ";

        if (!qname.equals("")) {
            sqlwhere += (" AND A_TITLE LIKE '%" + qname + "%' ");
            query.put("qname", qname);
        }

        sqlwhere += ")";

        /*抄送日期*/
        String sqlwhere1 = " ( 1=1 ";

        if (!qdate.equals("")) {
            sqlwhere1 += (" AND CC_DATE LIKE '%" + qdate + "%' ");
            query.put("qdate", DateTrimmer.getYMD_S_LBL(qdate));
        }

        sqlwhere1 += ")";

        String pageNO = StringTool.checkString(request.getParameter("pageNO"));

        String url = request.getContextPath() +
            "/system/flowlist.do?method=cclist&key=" + key;

        try {
            WObjectRegister register = WObjectRegister.getInstance();

            WObject object = register.findWObject(key);

            String sqlin = "SELECT WF_CC.A_ID FROM WF_CC,WF_ACTIVITY,WF_INSTANCE,WF_CATEGORY WHERE ";
            sqlin += ("WF_CC.A_ID = WF_ACTIVITY.A_ID AND WF_ACTIVITY.INS_ID = WF_INSTANCE.INS_ID AND " +
            "WF_INSTANCE.CG_ID = WF_CATEGORY.CG_ID AND WF_CATEGORY.OBJ_NAME = '" +
            object.ikey() + "' AND " + sqlwhere);

            if (!qunit.equals("")) {
                sqlin += (" AND INS_CUNITNAME LIKE '%" + qunit + "%'");
                query.put("qunit", qunit);
            }

            if (!qcreator.equals("")) {
                sqlin += (" AND INS_CREATORNAME LIKE '%" + qcreator + "%'");
                query.put("qcreator", qcreator);
            }

            if (!qcreatedate.equals("")) {
                sqlin += (" AND INS_START LIKE '%" + qcreatedate + "%'");
                query.put("qcreatedate", DateTrimmer.getYMD_S_LBL(qcreatedate));
            }

            String sql1 =
                "SELECT A_ID,CC_DATE FROM WF_CC WHERE OBJ_TYPE = 'u' AND OBJ_ID = '" +
                user.id() + "' AND A_ID IN (" + sqlin + ")";

            String sql2 =
                "SELECT A_ID,CC_DATE FROM WF_CC WHERE OBJ_TYPE = 'u' AND OBJ_ID = '" +
                user.id() + "' AND A_ID IN (" + sqlin + ")";

            if (user.igroup() != null) {
                String lumeid = UserManager.iallgroupflumeid(",");

                String sqlunion =
                    " UNION SELECT A_ID,CC_DATE FROM WF_CC WHERE OBJ_TYPE = 'g' AND OBJ_ID in (" +
                    lumeid + ") AND A_ID IN (" + sqlin + ")";

                sql1 = "SELECT COUNT(*) FROM (" + sql1 + sqlunion +
                    ") A WHERE " + sqlwhere1;

                sql2 = "SELECT A.A_ID,A.CC_DATE FROM (" + sql2 + sqlunion +
                    ") A WHERE " + sqlwhere1 + " ORDER BY CC_DATE DESC";
                ;
            } else {
                sql1 = "SELECT COUNT(*) FROM (" + sql1 + ") A WHERE " +
                    sqlwhere1;
                sql2 = "SELECT A.A_ID,A.CC_DATE FROM (" + sql2 + ") A WHERE " +
                    sqlwhere1 + " ORDER BY CC_DATE DESC";
                ;
            }

            SystemDataSource ds = SystemDataSource.getInstance();
            conn = ds.getConnection();

            QueryJack plus = QueryJackFactory.getInstance().createQueryJack(false);

            // 设置
            plus.setParameter(QueryJackFactory.PAGINATION_SQL1, sql1);
            plus.setParameter(QueryJackFactory.PAGINATION_SQL2, sql2);
            plus.setParameter(QueryJackFactory.DATABASE_CONNECTION, conn);
            plus.setParameter(QueryJackFactory.PAGINATION_LINKSTRING, url);
            plus.setParameter(QueryJackFactory.PAGINATION_COUNT, "20");
            plus.setParameter(QueryJackFactory.PAGINATION_PAGENO, pageNO);

            // plus.setParameter(QueryJackFactory.PAGINATION_UPDATEPAGENO, "");
            List resultList = plus.work();

            WorkflowManager manager = new WorkflowManager(conn);

            if ((resultList != null) && !resultList.isEmpty()) {
                WInstanceManager imanager = new WInstanceManager(conn);

                for (int i = 0; i < resultList.size(); i++) {
                    Map info = (Map) resultList.get(i);
                    String number = (String) info.get("PAGINATION_NUMBER");
                    String aid = (String) info.get("A_ID");
                    String date = (String) info.get("CC_DATE");
                    WActivity activity = imanager.getActivityByID(aid);

                    resultList.set(i, new Object[] { number, activity, date });
                }
            }

            // 获得跳转控制
            Controller controller = plus.getController("form1");

            // 设置调转类型
            controller.setType("input");

            // 显示标签
            Label label = plus.getLabel();

            // 设置显示标签类型
            label.setType("href");

            Object[] result = new Object[4];
            result[0] = resultList;
            result[1] = label;
            result[2] = controller;
            result[3] = pageNO;

            request.setAttribute("result", result);

            request.setAttribute("object", object);

            request.setAttribute("query", query);

            request.setAttribute("type", "cc");

            forward = new ActionForward();
            forward.setPath(object.iview4cc());
        } catch (Exception ex) {
            forward = mapping.findForward("error");
            ISystem.catchException(request, this, ex);
        } finally {
            SystemDataSource.closeConnection(conn);
        }

        return forward;
    }

    /**
     * 归档实例列表
     *
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward pigeonholelist(ActionMapping mapping, ActionForm form,
        HttpServletRequest request, HttpServletResponse response)
        throws Exception {
        ActionForward forward = null;
        Connection conn = null;

        IUser user = ISystem.getSessionUser(request);

        SimpleBean parm = new CommonBean4HSQ(request);

        Map query = new HashMap();

        /*公文类别*/
        String key = StringTool.checkString(request.getParameter("key"));

        /*标题*/
        String qname = StringTool.checkString(parm.getResource().get("qname"));

        /*接收日期*/
        String qdate = StringTool.checkString(parm.getResource().get("qdate"));

        /*起草人所在部门*/
        String qunit = StringTool.checkString(parm.getResource().get("qunit"));

        /*起草人*/
        String qcreator = StringTool.checkString(parm.getResource().get("qcreator"));

        /*起草日期*/
        String qcreatedate = StringTool.checkString(parm.getResource().get("qcreatedate"));

        qdate = DateTrimmer.formatDateForVarchar(qdate);

        qcreatedate = DateTrimmer.formatDateForVarchar(qcreatedate);

        String pageNO = StringTool.checkString(request.getParameter("pageNO"));

        String url = request.getContextPath() +
            "/system/flowlist.do?method=pigeonholelist&key=" + key;

        try {
            SystemDataSource ds = SystemDataSource.getInstance();
            conn = ds.getConnection();

            String sqlin = "";

            if (ISystem.matchrole(user, "flowmanager")) {
            } else if (ISystem.matchrole(user, "groupflowmanager")) {
                IGroup group = user.igroup();

                if (group != null) {

                    String idgather = group.iflumeid(",");

                    if (idgather.indexOf(",") > -1) {
                        String[] idstrs = idgather.split(",");

                        String idstr = "";

                        for (int i = 0; i < idstrs.length; i++) {
                            if (i < (idstrs.length - 1)) {
                                idstr += ("'" + idstrs[i] + "',");
                            } else {
                                idstr += ("'" + idstrs[i] + "'");
                            }
                        }

                        sqlin += (" AND WF_INSTANCE.INS_CUNITID IN (" + idstr +
                        ")");
                    } else {
                        sqlin += (" AND WF_INSTANCE.INS_CUNITID = '" +
                        idgather + "' ");
                    }
                } else {
                    sqlin = " AND 1 = -1 ";
                }
            } else {
                sqlin = " AND 1 = -1 ";
            }

            if (!qname.equals("")) {
                sqlin += (" AND INS_ID IN (SELECT INS_ID FROM WF_ACTIVITY WHERE A_TITLE LIKE '%" +
                qname + "%' GROUP BY INS_ID) ");

                query.put("qname", qname);
            }

            String sqlwhere = "";

            String sql1 =
                "SELECT COUNT(INS_ID) FROM WF_INSTANCE,WF_CATEGORY WHERE WF_INSTANCE.CG_ID = WF_CATEGORY.CG_ID " +
                "AND INS_STATE = 2 ";

            String sql2 =
                "SELECT INS_ID FROM WF_INSTANCE,WF_CATEGORY WHERE WF_INSTANCE.CG_ID = WF_CATEGORY.CG_ID " +
                "AND INS_STATE = 2 ";

            if (!key.equals("")) {
                sql1 += (" AND WF_CATEGORY.OBJ_NAME = '" + key + "' ");

                sql2 += (" AND WF_CATEGORY.OBJ_NAME = '" + key + "' ");
            }

            if (!qunit.equals("")) {
                sqlwhere += (" AND INS_CUNITNAME LIKE '%" + qunit + "%'");
                query.put("qunit", qunit);
            }

            if (!qcreator.equals("")) {
                sqlwhere += (" AND INS_CREATORNAME LIKE '%" + qcreator + "%'");
                query.put("qcreator", qcreator);
            }

            if (!qcreatedate.equals("")) {
                sqlwhere += (" AND INS_START LIKE '%" + qcreatedate + "%'");
                query.put("qcreatedate", DateTrimmer.getYMD_LBL(qcreatedate));
            }

            sql1 += (sqlwhere + sqlin);

            sql2 += (sqlwhere + sqlin + " ORDER BY INS_START DESC");

            QueryJack plus = QueryJackFactory.getInstance().createQueryJack(false);

            // 设置
            plus.setParameter(QueryJackFactory.PAGINATION_SQL1, sql1);
            plus.setParameter(QueryJackFactory.PAGINATION_SQL2, sql2);
            plus.setParameter(QueryJackFactory.DATABASE_CONNECTION, conn);
            plus.setParameter(QueryJackFactory.PAGINATION_LINKSTRING, url);
            plus.setParameter(QueryJackFactory.PAGINATION_COUNT, "20");
            plus.setParameter(QueryJackFactory.PAGINATION_PAGENO, pageNO);

            // plus.setParameter(QueryJackFactory.PAGINATION_UPDATEPAGENO, "");
            List resultList = plus.work();

            if ((resultList != null) && !resultList.isEmpty()) {
                WInstanceManager imanager = new WInstanceManager(conn);

                for (int i = 0; i < resultList.size(); i++) {
                    Map info = (Map) resultList.get(i);
                    String number = (String) info.get("PAGINATION_NUMBER");
                    String insid = (String) info.get("INS_ID");

                    WInstance instance = imanager.getInstanceByID(insid);

                    resultList.set(i, new Object[] { number, instance });
                }
            }

            // 获得跳转控制
            Controller controller = plus.getController("form1");

            // 设置调转类型
            controller.setType("input");

            // 显示标签
            Label label = plus.getLabel();

            // 设置显示标签类型
            label.setType("href");

            Object[] result = new Object[4];
            result[0] = resultList;
            result[1] = label;
            result[2] = controller;
            result[3] = pageNO;

            request.setAttribute("result", result);

            request.setAttribute("query", query);

            request.setAttribute("key", key);

            forward = mapping.findForward("pigeonholelist");
        } catch (Exception ex) {
            forward = mapping.findForward("error");
            ISystem.catchException(request, this, ex);
        } finally {
            SystemDataSource.closeConnection(conn);
        }

        return forward;
    }

    /**
     * 我参与办理的流程,已办结实例列表
     *
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward myendlist(ActionMapping mapping, ActionForm form,
        HttpServletRequest request, HttpServletResponse response)
        throws Exception {
        ActionForward forward = null;
        Connection conn = null;

        IUser user = ISystem.getSessionUser(request);

        SimpleBean parm = new CommonBean4HSQ(request);

        Map query = new HashMap();

        /*公文类别*/
        String key = StringTool.checkString(request.getParameter("key"));

        /*标题*/
        String qname = StringTool.checkString(parm.getResource().get("qname"));

        /*接收日期*/
        String qdate = StringTool.checkString(parm.getResource().get("qdate"));

        /*起草人所在部门*/
        String qunit = StringTool.checkString(parm.getResource().get("qunit"));

        /*起草人*/
        String qcreator = StringTool.checkString(parm.getResource().get("qcreator"));

        /*起草日期*/
        String qcreatedate = StringTool.checkString(parm.getResource().get("qcreatedate"));

        qdate = DateTrimmer.formatDateForVarchar(qdate);

        qcreatedate = DateTrimmer.formatDateForVarchar(qcreatedate);

        String pageNO = StringTool.checkString(request.getParameter("pageNO"));

        String url = request.getContextPath() +
            "/system/flowlist.do?method=endlist&key=" + key;

        try {
            SystemDataSource ds = SystemDataSource.getInstance();
            conn = ds.getConnection();

            String sqlin =
                " AND INS_ID IN (SELECT INS_ID FROM WF_ACTIVITY,WF_EXECUTOR WHERE WF_ACTIVITY.A_ID = " +
                "WF_EXECUTOR.A_ID AND WF_EXECUTOR.USER_ID = '" + user.id() +
                "' GROUP BY INS_ID) ";

            if (!qname.equals("")) {
                sqlin = " AND INS_ID IN (SELECT INS_ID FROM WF_ACTIVITY,WF_EXECUTOR WHERE WF_ACTIVITY.A_ID = " +
                    "WF_EXECUTOR.A_ID AND WF_EXECUTOR.USER_ID = '" + user.id() +
                    "' AND A_TITLE LIKE '%" + qname + "%' GROUP BY INS_ID) ";

                query.put("qname", qname);
            }

            String sqlwhere = "";

            String sql1 =
                "SELECT COUNT(INS_ID) FROM WF_INSTANCE,WF_CATEGORY WHERE WF_INSTANCE.CG_ID = WF_CATEGORY.CG_ID " +
                "AND INS_STATE = 1 ";

            String sql2 =
                "SELECT INS_ID FROM WF_INSTANCE,WF_CATEGORY WHERE WF_INSTANCE.CG_ID = WF_CATEGORY.CG_ID " +
                "AND INS_STATE = 1 ";

            if (!key.equals("")) {
                sql1 += (" AND WF_CATEGORY.OBJ_NAME = '" + key + "' ");

                sql2 += (" AND WF_CATEGORY.OBJ_NAME = '" + key + "' ");
            }

            if (!qunit.equals("")) {
                sqlwhere += (" AND INS_CUNITNAME LIKE '%" + qunit + "%'");
                query.put("qunit", qunit);
            }

            if (!qcreator.equals("")) {
                sqlwhere += (" AND INS_CREATORNAME LIKE '%" + qcreator + "%'");
                query.put("qcreator", qcreator);
            }

            if (!qcreatedate.equals("")) {
                sqlwhere += (" AND INS_START LIKE '%" + qcreatedate + "%'");
                query.put("qcreatedate", DateTrimmer.getYMD_LBL(qcreatedate));
            }

            sql1 += (sqlwhere + sqlin);

            sql2 += (sqlwhere + sqlin + " ORDER BY INS_START DESC");

            QueryJack plus = QueryJackFactory.getInstance().createQueryJack(false);

            // 设置
            plus.setParameter(QueryJackFactory.PAGINATION_SQL1, sql1);
            plus.setParameter(QueryJackFactory.PAGINATION_SQL2, sql2);
            plus.setParameter(QueryJackFactory.DATABASE_CONNECTION, conn);
            plus.setParameter(QueryJackFactory.PAGINATION_LINKSTRING, url);
            plus.setParameter(QueryJackFactory.PAGINATION_COUNT, "20");
            plus.setParameter(QueryJackFactory.PAGINATION_PAGENO, pageNO);

            // plus.setParameter(QueryJackFactory.PAGINATION_UPDATEPAGENO, "");
            List resultList = plus.work();

            if ((resultList != null) && !resultList.isEmpty()) {
                WInstanceManager imanager = new WInstanceManager(conn);

                for (int i = 0; i < resultList.size(); i++) {
                    Map info = (Map) resultList.get(i);
                    String number = (String) info.get("PAGINATION_NUMBER");
                    String insid = (String) info.get("INS_ID");

                    WInstance instance = imanager.getInstanceByID(insid);

                    resultList.set(i, new Object[] { number, instance });
                }
            }

            // 获得跳转控制
            Controller controller = plus.getController("form1");

            // 设置调转类型
            controller.setType("input");

            // 显示标签
            Label label = plus.getLabel();

            // 设置显示标签类型
            label.setType("href");

            Object[] result = new Object[4];
            result[0] = resultList;
            result[1] = label;
            result[2] = controller;
            result[3] = pageNO;

            request.setAttribute("result", result);

            request.setAttribute("query", query);

            request.setAttribute("key", key);

            forward = mapping.findForward("myendlist");
        } catch (Exception ex) {
            forward = mapping.findForward("error");
            ISystem.catchException(request, this, ex);
        } finally {
            SystemDataSource.closeConnection(conn);
        }

        return forward;
    }

    /**
     * 我参与办理的流程,已办结实例列表
     *
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward endlist4json(ActionMapping mapping, ActionForm form,
        HttpServletRequest request, HttpServletResponse response)
        throws Exception {
        ActionForward forward = null;
        Connection conn = null;

        IUser user = ISystem.getSessionUser(request);

        SimpleBean parm = new CommonBean4HSQ(request);

        Map query = new HashMap();

        /*公文类别*/
        String key = StringTool.checkString(request.getParameter("key"));

        /*标题*/
        String qname = StringTool.checkString(parm.getResource().get("qname"));

        /*接收日期*/
        String qdate = StringTool.checkString(parm.getResource().get("qdate"));

        /*起草人所在部门*/
        String qunit = StringTool.checkString(parm.getResource().get("qunit"));

        /*起草人*/
        String qcreator = StringTool.checkString(parm.getResource().get("qcreator"));

        /*起草日期*/
        String qcreatedate = StringTool.checkString(parm.getResource().get("qcreatedate"));

        qdate = DateTrimmer.formatDateForVarchar(qdate);

        qcreatedate = DateTrimmer.formatDateForVarchar(qcreatedate);

        String pageNO = StringTool.checkString(request.getParameter("pageNO"));

        String url = request.getContextPath() +
            "/system/flowlist.do?method=endlist&key=" + key;

        try {
            SystemDataSource ds = SystemDataSource.getInstance();
            conn = ds.getConnection();

            String sqlin =
                " AND INS_ID IN (SELECT INS_ID FROM WF_ACTIVITY,WF_EXECUTOR WHERE WF_ACTIVITY.A_ID = " +
                "WF_EXECUTOR.A_ID AND WF_EXECUTOR.USER_ID = '" + user.id() +
                "' GROUP BY INS_ID) ";

            if (!qname.equals("")) {
                sqlin = " AND INS_ID IN (SELECT INS_ID FROM WF_ACTIVITY,WF_EXECUTOR WHERE WF_ACTIVITY.A_ID = " +
                    "WF_EXECUTOR.A_ID AND WF_EXECUTOR.USER_ID = '" + user.id() +
                    "' AND A_TITLE LIKE '%" + qname + "%' GROUP BY INS_ID) ";

                query.put("qname", qname);
            }

            String sqlwhere = "";

            String sql1 =
                "SELECT COUNT(INS_ID) FROM WF_INSTANCE,WF_CATEGORY WHERE WF_INSTANCE.CG_ID = WF_CATEGORY.CG_ID " +
                "AND INS_STATE = 1 ";

            String sql2 =
                "SELECT INS_ID FROM WF_INSTANCE,WF_CATEGORY WHERE WF_INSTANCE.CG_ID = WF_CATEGORY.CG_ID " +
                "AND INS_STATE = 1 ";

            if (!key.equals("")) {
                sql1 += (" AND WF_CATEGORY.OBJ_NAME = '" + key + "' ");

                sql2 += (" AND WF_CATEGORY.OBJ_NAME = '" + key + "' ");
            }

            if (!qunit.equals("")) {
                sqlwhere += (" AND INS_CUNITNAME LIKE '%" + qunit + "%'");
                query.put("qunit", qunit);
            }

            if (!qcreator.equals("")) {
                sqlwhere += (" AND INS_CREATORNAME LIKE '%" + qcreator + "%'");
                query.put("qcreator", qcreator);
            }

            if (!qcreatedate.equals("")) {
                sqlwhere += (" AND INS_START LIKE '%" + qcreatedate + "%'");
                query.put("qcreatedate", DateTrimmer.getYMD_LBL(qcreatedate));
            }

            sql1 += (sqlwhere + sqlin);

            sql2 += (sqlwhere + sqlin + " ORDER BY INS_START DESC");

            QueryJack plus = QueryJackFactory.getInstance().createQueryJack(false);

            // 设置
            plus.setParameter(QueryJackFactory.PAGINATION_SQL1, sql1);
            plus.setParameter(QueryJackFactory.PAGINATION_SQL2, sql2);
            plus.setParameter(QueryJackFactory.DATABASE_CONNECTION, conn);
            plus.setParameter(QueryJackFactory.PAGINATION_LINKSTRING, url);
            plus.setParameter(QueryJackFactory.PAGINATION_COUNT, "20");
            plus.setParameter(QueryJackFactory.PAGINATION_PAGENO, pageNO);

            // plus.setParameter(QueryJackFactory.PAGINATION_UPDATEPAGENO, "");
            List resultList = plus.work();

            if ((resultList != null) && !resultList.isEmpty()) {
                WInstanceManager imanager = new WInstanceManager(conn);

                for (int i = 0; i < resultList.size(); i++) {
                    Map info = (Map) resultList.get(i);

                    String insid = (String) info.get("INS_ID");

                    WInstance instance = imanager.getInstanceByID(insid);

                    WActivity act = instance.imaxactivity();

                    info.put("A_ID", act.id());
                    info.put("A_TITLE", act.ititle());
                    info.put("S_NAME", act.istep().iname());
                    info.put("A_STATE", act.istate());
                    info.put("A_STIME", act.istarttime());
                    info.put("A_LTIME", act.ilmtime());
                    info.put("INS_OBJECT",
                        act.instance().iflow().iobject().iname());
                    info.put("CREATOR", instance.icreatorname());
                    info.put("UNIT", instance.iunitname());
                    info.put("USERUNIT", act.iexecutor().iunitname());
                    info.put("USERNAME", act.iexecutor().iusername());
                }
            }

            // 获得跳转控制
            Controller controller = plus.getController("form1");

            // 设置调转类型
            controller.setType("input");

            // 显示标签
            Label label = plus.getLabel();

            // 设置显示标签类型
            label.setType("href");

            Object[] result = new Object[4];
            result[0] = resultList;
            result[1] = label;
            result[2] = controller;
            result[3] = pageNO;

            request.setAttribute("result", result);

            request.setAttribute("query", query);

            request.setAttribute("key", key);

            ListRange lr = new ListRange();
            lr.setTotalSize(label.totaldata());
            lr.setList(resultList);
            lr.setSuccess(true);
            outJson(lr, response);
        } catch (Exception ex) {
            forward = mapping.findForward("error");
            ISystem.catchException(request, this, ex);
        } finally {
            SystemDataSource.closeConnection(conn);
        }

        return forward;
    }

    /**
     * 已办结实例列表
     *
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward endlist(ActionMapping mapping, ActionForm form,
        HttpServletRequest request, HttpServletResponse response)
        throws Exception {
        ActionForward forward = null;
        Connection conn = null;

        IUser user = ISystem.getSessionUser(request);

        SimpleBean parm = new CommonBean4HSQ(request);

        Map query = new HashMap();

        /*公文类别*/
        String key = StringTool.checkString(request.getParameter("key"));

        /*标题*/
        String qname = StringTool.checkString(parm.getResource().get("qname"));

        /*接收日期*/
        String qdate = StringTool.checkString(parm.getResource().get("qdate"));

        /*起草人所在部门*/
        String qunit = StringTool.checkString(parm.getResource().get("qunit"));

        /*起草人*/
        String qcreator = StringTool.checkString(parm.getResource().get("qcreator"));

        /*起草日期*/
        String qcreatedate = StringTool.checkString(parm.getResource().get("qcreatedate"));

        qdate = DateTrimmer.formatDateForVarchar(qdate);

        qcreatedate = DateTrimmer.formatDateForVarchar(qcreatedate);

        String pageNO = StringTool.checkString(request.getParameter("pageNO"));

        String url = request.getContextPath() +
            "/system/flowlist.do?method=endlist&key=" + key;

        try {
            SystemDataSource ds = SystemDataSource.getInstance();
            conn = ds.getConnection();

            String sqlin = "";

            if (ISystem.matchrole(user, "flowmanager")) {
            } else if (ISystem.matchrole(user, "groupflowmanager")) {
                IGroup group = user.igroup();

                if (group != null) {
                    String idgather = group.iflumeid(",");

                    if (idgather.indexOf(",") > -1) {
                        String[] idstrs = idgather.split(",");

                        String idstr = "";

                        for (int i = 0; i < idstrs.length; i++) {
                            if (i < (idstrs.length - 1)) {
                                idstr += ("'" + idstrs[i] + "',");
                            } else {
                                idstr += ("'" + idstrs[i] + "'");
                            }
                        }

                        sqlin += (" AND WF_INSTANCE.INS_CUNITID IN (" + idstr +
                        ")");
                    } else {
                        sqlin += (" AND WF_INSTANCE.INS_CUNITID = '" +
                        idgather + "' ");
                    }
                } else {
                    sqlin = " AND 1 = -1 ";
                }
            } else {
                sqlin = " AND 1 = -1 ";
            }

            if (!qname.equals("")) {
                sqlin += (" AND INS_ID IN (SELECT INS_ID FROM WF_ACTIVITY WHERE A_TITLE LIKE '%" +
                qname + "%' GROUP BY INS_ID) ");

                query.put("qname", qname);
            }

            String sqlwhere = "";

            String sql1 =
                "SELECT COUNT(INS_ID) FROM WF_INSTANCE,WF_CATEGORY WHERE WF_INSTANCE.CG_ID = WF_CATEGORY.CG_ID " +
                "AND INS_STATE = 1 ";

            String sql2 =
                "SELECT INS_ID FROM WF_INSTANCE,WF_CATEGORY WHERE WF_INSTANCE.CG_ID = WF_CATEGORY.CG_ID " +
                "AND INS_STATE = 1 ";

            if (!key.equals("")) {
                sql1 += (" AND WF_CATEGORY.OBJ_NAME = '" + key + "' ");

                sql2 += (" AND WF_CATEGORY.OBJ_NAME = '" + key + "' ");
            }

            if (!qunit.equals("")) {
                sqlwhere += (" AND INS_CUNITNAME LIKE '%" + qunit + "%'");
                query.put("qunit", qunit);
            }

            if (!qcreator.equals("")) {
                sqlwhere += (" AND INS_CREATORNAME LIKE '%" + qcreator + "%'");
                query.put("qcreator", qcreator);
            }

            if (!qcreatedate.equals("")) {
                sqlwhere += (" AND INS_START LIKE '%" + qcreatedate + "%'");
                query.put("qcreatedate", DateTrimmer.getYMD_LBL(qcreatedate));
            }

            sql1 += (sqlwhere + sqlin);

            sql2 += (sqlwhere + sqlin + " ORDER BY INS_START DESC");

            QueryJack plus = QueryJackFactory.getInstance().createQueryJack(false);

            // 设置
            plus.setParameter(QueryJackFactory.PAGINATION_SQL1, sql1);
            plus.setParameter(QueryJackFactory.PAGINATION_SQL2, sql2);
            plus.setParameter(QueryJackFactory.DATABASE_CONNECTION, conn);
            plus.setParameter(QueryJackFactory.PAGINATION_LINKSTRING, url);
            plus.setParameter(QueryJackFactory.PAGINATION_COUNT, "20");
            plus.setParameter(QueryJackFactory.PAGINATION_PAGENO, pageNO);

            // plus.setParameter(QueryJackFactory.PAGINATION_UPDATEPAGENO, "");
            List resultList = plus.work();

            if ((resultList != null) && !resultList.isEmpty()) {
                WInstanceManager imanager = new WInstanceManager(conn);

                for (int i = 0; i < resultList.size(); i++) {
                    Map info = (Map) resultList.get(i);
                    String number = (String) info.get("PAGINATION_NUMBER");
                    String insid = (String) info.get("INS_ID");

                    WInstance instance = imanager.getInstanceByID(insid);

                    resultList.set(i, new Object[] { number, instance });
                }
            }

            // 获得跳转控制
            Controller controller = plus.getController("form1");

            // 设置调转类型
            controller.setType("input");

            // 显示标签
            Label label = plus.getLabel();

            // 设置显示标签类型
            label.setType("href");

            Object[] result = new Object[4];
            result[0] = resultList;
            result[1] = label;
            result[2] = controller;
            result[3] = pageNO;

            request.setAttribute("result", result);

            request.setAttribute("query", query);

            request.setAttribute("key", key);

            forward = mapping.findForward("endlist");
        } catch (Exception ex) {
            forward = mapping.findForward("error");
            ISystem.catchException(request, this, ex);
        } finally {
            SystemDataSource.closeConnection(conn);
        }

        return forward;
    }

    /**
     * 撤销实例列表
     *
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward cancellist(ActionMapping mapping, ActionForm form,
        HttpServletRequest request, HttpServletResponse response)
        throws Exception {
        ActionForward forward = null;
        Connection conn = null;

        IUser user = ISystem.getSessionUser(request);

        CommonBean4HSQ cbh = new CommonBean4HSQ(request);

        Map param = cbh.getResource();

        Map query = new HashMap();

        /*公文类别*/
        String key = StringTool.checkString(param.get("key"));

        /*标题*/
        String qname = StringTool.checkString(param.get("qname"));

        /*接收日期*/
        String qdate = StringTool.checkString(param.get("qdate"));

        /*起草人所在部门*/
        String qunit = StringTool.checkString(param.get("qunit"));

        /*起草人*/
        String qcreator = StringTool.checkString(param.get("qcreator"));

        /*起草日期*/
        String qcreatedate = StringTool.checkString(param.get("qcreatedate"));

        qdate = DateTrimmer.formatDateForVarchar(qdate);

        qcreatedate = DateTrimmer.formatDateForVarchar(qcreatedate);

        String pageNO = StringTool.checkString(param.get("pageNO"));

        String url = request.getContextPath() +
            "/system/flowlist.do?method=cancellist";

        if (!key.equals("")) {
            url = request.getContextPath() +
                "/system/flowlist.do?method=cancellist&key=" + key;
        }

        try {
            SystemDataSource ds = SystemDataSource.getInstance();
            conn = ds.getConnection();

            String sqlin = "";

            if (ISystem.matchrole(user, "flowmanager")) {
            } else if (ISystem.matchrole(user, "groupflowmanager")) {
                IGroup group = user.igroup();

                if (group != null) {
                    String idgather = group.iflumeid(",");

                    if (idgather.indexOf(",") > -1) {
                        String[] idstrs = idgather.split(",");

                        String idstr = "";

                        for (int i = 0; i < idstrs.length; i++) {
                            if (i < (idstrs.length - 1)) {
                                idstr += ("'" + idstrs[i] + "',");
                            } else {
                                idstr += ("'" + idstrs[i] + "'");
                            }
                        }

                        sqlin += (" AND WF_INSTANCE.INS_CUNITID IN (" + idstr +
                        ")");
                    } else {
                        sqlin += (" AND WF_INSTANCE.INS_CUNITID = '" +
                        idgather + "' ");
                    }
                } else {
                    sqlin = " AND 1 = -1 ";
                }
            } else {
                sqlin = " AND 1 = -1 ";
            }

            if (!qname.equals("")) {
                sqlin += (" AND INS_ID IN (SELECT INS_ID FROM WF_ACTIVITY WHERE A_TITLE LIKE '%" +
                qname + "%' GROUP BY INS_ID) ");

                query.put("qname", qname);
            }

            String sqlwhere = "";

            if (!key.equals("")) {
                sqlwhere += (" AND WF_CATEGORY.OBJ_NAME = '" + key + "'");
            }

            String sql1 =
                "SELECT COUNT(INS_ID) FROM WF_INSTANCE,WF_CATEGORY WHERE WF_INSTANCE.CG_ID = WF_CATEGORY.CG_ID " +
                " AND INS_STATE = '-1' ";

            String sql2 =
                "SELECT INS_ID FROM WF_INSTANCE,WF_CATEGORY WHERE WF_INSTANCE.CG_ID = WF_CATEGORY.CG_ID " +
                " AND INS_STATE = '-1' ";

            if (!qunit.equals("")) {
                sqlwhere += (" AND INS_CUNITNAME LIKE '%" + qunit + "%'");
                query.put("qunit", qunit);
            }

            if (!qcreator.equals("")) {
                sqlwhere += (" AND INS_CREATORNAME LIKE '%" + qcreator + "%'");
                query.put("qcreator", qcreator);
            }

            if (!qcreatedate.equals("")) {
                sqlwhere += (" AND INS_START LIKE '%" + qcreatedate + "%'");
                query.put("qcreatedate", DateTrimmer.getYMD_LBL(qcreatedate));
            }

            sql1 += (sqlwhere + sqlin);

            sql2 += (sqlwhere + sqlin + " ORDER BY INS_START DESC");

            QueryJack plus = QueryJackFactory.getInstance().createQueryJack(false);

            // 设置
            plus.setParameter(QueryJackFactory.PAGINATION_SQL1, sql1);
            plus.setParameter(QueryJackFactory.PAGINATION_SQL2, sql2);
            plus.setParameter(QueryJackFactory.DATABASE_CONNECTION, conn);
            plus.setParameter(QueryJackFactory.PAGINATION_LINKSTRING, url);
            plus.setParameter(QueryJackFactory.PAGINATION_COUNT, "20");
            plus.setParameter(QueryJackFactory.PAGINATION_PAGENO, pageNO);

            // plus.setParameter(QueryJackFactory.PAGINATION_UPDATEPAGENO, "");
            List resultList = plus.work();

            if ((resultList != null) && !resultList.isEmpty()) {
                WorkflowManager manager = new WorkflowManager(conn);
                WInstanceManager imanager = new WInstanceManager(conn);

                for (int i = 0; i < resultList.size(); i++) {
                    Map info = (Map) resultList.get(i);
                    String number = (String) info.get("PAGINATION_NUMBER");
                    String insid = (String) info.get("INS_ID");

                    WInstance instance = imanager.getInstanceByID(insid);

                    resultList.set(i, new Object[] { number, instance });
                }
            }

            // 获得跳转控制
            Controller controller = plus.getController("form1");

            // 设置调转类型
            controller.setType("input");

            // 显示标签
            Label label = plus.getLabel();

            // 设置显示标签类型
            label.setType("href");

            Object[] result = new Object[4];
            result[0] = resultList;
            result[1] = label;
            result[2] = controller;
            result[3] = pageNO;

            request.setAttribute("result", result);

            request.setAttribute("query", query);

            request.setAttribute("key", key);

            forward = mapping.findForward("cancellist");
        } catch (Exception ex) {
            forward = mapping.findForward("error");
            ISystem.catchException(request, this, ex);
        } finally {
            SystemDataSource.closeConnection(conn);
        }

        return forward;
    }

    /**
     * 公文综合查询
     *
     * @param mapping
     * @param form
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    public ActionForward query(ActionMapping mapping, ActionForm form,
        HttpServletRequest request, HttpServletResponse response)
        throws Exception {
        ActionForward forward = null;
        Connection conn = null;

        IUser user = ISystem.getSessionUser(request);

        CommonBean4HSQ cbh = new CommonBean4HSQ(request);

        Map param = cbh.getResource();

        Map query = new HashMap();

        /*公文类别*/
        String key = StringTool.checkString(param.get("key"));

        /*标题*/
        String qname = StringTool.checkString(param.get("qname"));

        /*接收日期*/
        String qdate = StringTool.checkString(param.get("qdate"));

        /*起草人所在部门*/
        String qunit = StringTool.checkString(param.get("qunit"));

        /*起草人*/
        String qcreator = StringTool.checkString(param.get("qcreator"));

        /*起草日期*/
        String qcreatedate = StringTool.checkString(param.get("qcreatedate"));

        qdate = DateTrimmer.formatDateForVarchar(qdate);

        qcreatedate = DateTrimmer.formatDateForVarchar(qcreatedate);

        String pageNO = StringTool.checkString(param.get("pageNO"));

        String url = request.getContextPath() +
            "/system/flowlist.do?method=query&key=" + key;

        try {
            SystemDataSource ds = SystemDataSource.getInstance();
            conn = ds.getConnection();

            String sqlin = "";

            if (ISystem.matchrole(user, "flowmanager")) {
            } else if (ISystem.matchrole(user, "groupflowmanager")) {
                IGroup group = user.igroup();

                if (group != null) {
                    String idgather = group.iflumeid(",");

                    if (idgather.indexOf(",") > -1) {
                        String[] idstrs = idgather.split(",");

                        String idstr = "";

                        for (int i = 0; i < idstrs.length; i++) {
                            if (i < (idstrs.length - 1)) {
                                idstr += ("'" + idstrs[i] + "',");
                            } else {
                                idstr += ("'" + idstrs[i] + "'");
                            }
                        }

                        sqlin += (" AND WF_EXECUTOR.UNIT_ID IN (" + idstr +
                        ")");
                    } else {
                        sqlin += (" AND WF_EXECUTOR.UNIT_ID = '" + idgather +
                        "' ");
                    }
                } else {
                    sqlin = " AND 1 = -1 ";
                }
            } else {
                sqlin = " AND 1 = -1 ";
            }

            String sqlwhere = "";

            String sql2 = "SELECT MAX(WF_ACTIVITY.A_ID) AS A_ID " +
                "FROM WF_ACTIVITY,WF_EXECUTOR,WF_INSTANCE,WF_CATEGORY " +
                "WHERE WF_INSTANCE.CG_ID = WF_CATEGORY.CG_ID " +
                "AND WF_ACTIVITY.INS_ID = WF_INSTANCE.INS_ID AND WF_INSTANCE.INS_STATE = '0' " +
                "AND WF_ACTIVITY.A_ID = WF_EXECUTOR.A_ID ";

            if (!key.equals("")) {
                sql2 += (" AND WF_CATEGORY.OBJ_NAME = '" + key + "' ");
            }

            if (!qunit.equals("")) {
                sqlwhere += (" AND WF_INSTANCE.INS_CUNITNAME LIKE '%" + qunit +
                "%'");
                query.put("qunit", qunit);
            }

            if (!qcreator.equals("")) {
                sqlwhere += (" AND WF_INSTANCE.INS_CREATORNAME LIKE '%" +
                qcreator + "%'");
                query.put("qcreator", qcreator);
            }

            if (!qcreatedate.equals("")) {
                sqlwhere += (" AND WF_INSTANCE.INS_START LIKE '%" +
                qcreatedate + "%'");

                //query.put("qcreatedate", DateTrimmer.getYMD_S_LBL(qcreatedate));
                query.put("qcreatedate", DateTrimmer.getYMD_LBL(qcreatedate));
            }

            if (!qname.equals("")) {
                sqlwhere += (" AND WF_ACTIVITY.A_TITLE LIKE '%" + qname +
                "%' ");
                query.put("qname", qname);
            }

            sql2 += (sqlwhere + sqlin +
            " GROUP BY WF_ACTIVITY.INS_ID,WF_INSTANCE.INS_START ");

            String sql1 = "SELECT COUNT(*) FROM WF_ACTIVITY WHERE A_ID IN (" +
                sql2 + ")";

            QueryJack plus = QueryJackFactory.getInstance().createQueryJack(false);

            // 设置
            plus.setParameter(QueryJackFactory.PAGINATION_SQL1, sql1);
            plus.setParameter(QueryJackFactory.PAGINATION_SQL2,
                sql2 + "ORDER BY WF_INSTANCE.INS_START DESC");
            plus.setParameter(QueryJackFactory.DATABASE_CONNECTION, conn);
            plus.setParameter(QueryJackFactory.PAGINATION_LINKSTRING, url);
            plus.setParameter(QueryJackFactory.PAGINATION_COUNT, "20");
            plus.setParameter(QueryJackFactory.PAGINATION_PAGENO, pageNO);

            // plus.setParameter(QueryJackFactory.PAGINATION_UPDATEPAGENO, "");
            List resultList = plus.work();

            if ((resultList != null) && !resultList.isEmpty()) {
                WInstanceManager manager = new WInstanceManager(conn);

                for (int i = 0; i < resultList.size(); i++) {
                    Map info = (Map) resultList.get(i);
                    String number = (String) info.get("PAGINATION_NUMBER");
                    String actid = (String) info.get("A_ID");

                    WActivity act = manager.getActivityByID(actid);

                    resultList.set(i, new Object[] { number, act });
                }
            }

            // 获得跳转控制
            Controller controller = plus.getController("form1");

            // 设置调转类型
            controller.setType("input");

            // 显示标签
            Label label = plus.getLabel();

            // 设置显示标签类型
            label.setType("href");

            Object[] result = new Object[4];
            result[0] = resultList;
            result[1] = label;
            result[2] = controller;
            result[3] = pageNO;

            request.setAttribute("result", result);

            request.setAttribute("query", query);

            request.setAttribute("key", key);

            forward = mapping.findForward("query");
        } catch (Exception ex) {
            forward = mapping.findForward("error");
            ISystem.catchException(request, this, ex);
        } finally {
            SystemDataSource.closeConnection(conn);
        }

        return forward;
    }

    public void outJsonString(String str, HttpServletResponse response) {
        response.setContentType("text/javascript;charset=UTF-8");
        outString(str, response);
    }

    public void outJson(Object obj, HttpServletResponse response) {
        outJsonString(JSONObject.fromObject(obj).toString(), response);
    }

    public void outString(String str, HttpServletResponse response) {
        try {
            PrintWriter out = response.getWriter();

            // System.out.println(str);
            out.write(str);
        } catch (IOException e) {
        }
    }
}
